package com.zhdl.modules.prefiresys.repository;

import com.project.common.repository.BaseRepository;
import com.zhdl.modules.prefiresys.entity.DefAreaAlarmRec;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface DefAreaAlarmRecRepository extends BaseRepository<DefAreaAlarmRec, String> {

    /**
     * 查询 七天报警数据
     *
     * @return
     */
    @Query(value = """
            SELECT DATE_FORMAT(startTime, '%Y-%m-%d') AS date, COUNT(*) AS count
            FROM defarea_alarm_rec
            WHERE startTime >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
            GROUP BY date
            ORDER BY date
                        """, nativeQuery = true)
    List<Object[]> statAgoWeekAlarmCount();


    /**
     * 查询七天内的报警信息
     * 两次正常算一次 所以 当报警为0或者-1时，为一次报警
     *
     * @return
     */
    @Query(value = """
            SELECT DATE_FORMAT(startTime, '%Y-%m-%d') AS date, COUNT(*) AS count
            FROM defarea_alarm_rec
            WHERE startTime >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND (alarmStatus = 0 or alarmStatus = -1)
            GROUP BY date
            ORDER BY date
                        """, nativeQuery = true)
    List<Object[]> statAgoWeekAlarmCount_1();


    @Query(value = "SELECT * FROM defarea_alarm_rec  ORDER BY create_date DESC LIMIT 1", nativeQuery = true)
    DefAreaAlarmRec findNewData();


    /**
     * 查询一个月内 三级报警数据
     *
     * @return
     */
    @Query(value = """
            SELECT DATE_FORMAT(startTime, '%Y-%m-%d') AS date, COUNT(*) AS count
            FROM defarea_alarm_rec
            WHERE startTime >= DATE_SUB(CURDATE(), INTERVAL 31 DAY) AND nextAlarmStatus = 2
            GROUP BY date
            ORDER BY date
                        """, nativeQuery = true)
    List<Object[]> statMonthAlarmNo3();

    /**
     * 查询一个月内 二级报警数据
     *
     * @return
     */
    @Query(value = """
            SELECT DATE_FORMAT(startTime, '%Y-%m-%d') AS date, COUNT(*) AS count
            FROM defarea_alarm_rec
            WHERE startTime >= DATE_SUB(CURDATE(), INTERVAL 31 DAY) AND nextAlarmStatus = 3
            GROUP BY date
            ORDER BY date
                        """, nativeQuery = true)
    List<Object[]> statMonthAlarmNo2();

    /**
     * 查询一个月内 一级报警数据
     *
     * @return
     */
    @Query(value = """
            SELECT DATE_FORMAT(startTime, '%Y-%m-%d') AS date, COUNT(*) AS count
            FROM defarea_alarm_rec
            WHERE startTime >= DATE_SUB(CURDATE(), INTERVAL 31 DAY) AND nextAlarmStatus = 4
            GROUP BY date
            ORDER BY date
                        """, nativeQuery = true)
    List<Object[]> statMonthAlarmNo1();
}
